import pymysql
import types

class Mysql(object):
    def __init__(self):
        try:
            self.conn=pymysql.connect(
                host='192.168.26.131',
                port=3306,
                user='root',
                password='adminroot',
                db='mysql',
                charset='utf8'
            )
        except Exception:
            print(Exception)
        else:
            print("链接成功")
            self.cur=self.conn.cursor()

    def reate_table(self):
        sql= "create table if not exists 'testtb' " \
             "(id int(11) NOT NULL auto_increment," \
             "name varchar(10) not null," \
             "age int," \
             "sex varchar(2))ENGINE=innoDB default charset=utf8 auto_increment=1"
        res =self.cur.execute(sql)
        print(res)

    def close(self):
        self.cur.close()
        self.conn.close()

    def add(self): # 增
        sql="insert into testtb values(1,'bob',18,'man'),(2,'Tom',21,'man'),(3,'john',16,'women')"
        res=self.cur.execute(sql)
        if res:
            self.conn.commit()
        else:
            self.conn.rollback()
        print(res)

    def remove(self): # 删
        sql='delte from testtb where id=1'
        res=self.cur.execute(sql)
        if res:
            self.conn.commit()
        else:
            self.conn.rollback()
        print(res)

    def mod(self): # 改
        sql = 'update testtb set name="Tom Ding" where id=2'
        res = self.cur.execute(sql)
        if res:
            self.conn.commit()
        else:
            self.conn.rollback()
        print(res)

    def show(self):  # 查
        sql = 'select * from testtb'
        self.cur.execute(sql)
        res = self.cur.fetchall()
        for i in res:
            print(i)

if __name__ == '__main__':
    mysql = Mysql()
    mysql.create_table()
    mysql.add()
    mysql.mod()
    mysql.rem()
    mysql.show()
    mysql.close()
